﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

namespace Catering_Rules
{
    public partial class DataViewForReport
    {
        private readonly Catering_Operation.DataViewForReport dal = new Catering_Operation.DataViewForReport();

        #region 消耗计算
        /// <summary>
        /// 年条件查询
        /// </summary>
        /// <param name="ClassID"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable GetXiaohao_CalRuKu(string strarealist, string AREAID, string CLASSID, string year)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(year) && Convert.ToInt32(year) > 1949)
            {
                where.Append(string.Format(" and F.[Year] = {0}", year));
            }
            return dal.GetXiaohao_Cal(where.ToString());
        }

        /// <summary>
        /// 月条件查询
        /// </summary>
        /// <param name="ClassID"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable GetXiaohao_CalForMonth(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and F.[Year] = {0} and F.[Month] = {1}", DTIME.Year, DTIME.Month));
            }
            return dal.GetXiaohao_CalForMonth(where.ToString());
        }

        /// <summary>
        /// 获取盘点
        /// </summary>
        /// <param name="rawid"></param>
        /// <param name="Year"></param>
        /// <param name="CLASSID"></param>
        /// <returns></returns>
        public string GetInventory(string rawid, int Year, string CLASSID)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and RawID = {0}", rawid));
            where.Append(string.Format(" and year(AddDate) < {0}", Year));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID={0}", CLASSID));
            }
            return dal.GetInventory(where.ToString());
        }
        public string GetInventory(string rawid, int Year, int Month, string CLASSID)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and RawID = {0}", rawid));
            where.Append(string.Format(" and AddDate<'{0}'", new DateTime(Year, Month, 1)));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID={0}", CLASSID));
            }
            return dal.GetInventory(where.ToString());
        }

        /// <summary>
        /// 年条件查询
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetViewFromRecordsXiaoHaoCal(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and F.[Year] = {0}", DTIME.Year));
            }
            return dal.GetViewFromRecordsXiaoHaoCal(where.ToString());
        }

        /// <summary>
        /// 月条件查询
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetViewFromRecordsXiaoHaoCalByMonth(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and F.[Year] = {0} and F.[Month] = {1}", DTIME.Year, DTIME.Month));
            }
            return dal.GetViewFromRecordsXiaoHaoCalByMonth(where.ToString());
        }


        #endregion

        #region 消耗分类汇总
        /// <summary>
        /// 年条件查询
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetXiaohao_TypeSum(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and F.[Year] = {0}", DTIME.Year));
            }
            return dal.GetViewFromRecordsViewMonitor(where.ToString());
        }

        /// <summary>
        /// 月条件查询
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetXiaohao_TypeSumForMonth(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            where.Append("and RTID <> 1 and RTID <> 2 and RTID <> 9 and RTID <> 5 and RTID <> 12 ");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and F.GroupID = {0}", AREAID));
            }
            else if (strarealist.Length > 0)
            {
                where.Append(" and F.GroupID in (" + strarealist + ") ");
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and F.ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and F.[Year] = {0} and F.[Month] = {1}", DTIME.Year, DTIME.Month));
            }
            return dal.GetViewSumToDataTableForMonth(where.ToString());
        }

        /// <summary>
        /// 营养分析
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetNutrientCompositionGroupByMonth(string AREAID, string CLASSID, int YEAR)
        {
            return dal.GetNutrientCompositionGroupByMonth(AREAID, CLASSID, YEAR);
        }
        #endregion

        #region 累计百分比
        /// <summary>
        /// 年条件查询
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetTotalPercent(string strarealist, string AREAID, string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (Convert.ToInt32(YEAR) > 2011)
            {
                if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
                {
                    where.Append(string.Format(" and dbo.FromRecords.GroupID = {0}", AREAID));
                }
                else if (!string.IsNullOrEmpty(strarealist))
                {
                    where.Append(string.Format(" and dbo.FromRecords.GroupID IN ( {0} )", strarealist));
                }
                if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
                {
                    where.Append(string.Format(" and dbo.FromRecords.ClassID = {0}", CLASSID));
                }
                if (!string.IsNullOrEmpty(YEAR))
                {
                    where.Append(string.Format(" and YEAR(dbo.FromRecords.OutDate) = {0}", YEAR));
                }
                dt = dal.GetTotalPercent(where.ToString());
            }
            else
            {
                if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
                {
                    where.Append(string.Format(" and ClassID = {0}", CLASSID));
                }
                if (!string.IsNullOrEmpty(YEAR))
                {
                    where.Append(string.Format(" and YEAR(InDate) = {0}", YEAR));
                }
                dt = dal.GetTotalPercentHistoryDate(where.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 月条件
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetTotalPercentForMonth(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and dbo.FromRecords.GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" and dbo.FromRecords.GroupID IN ( {0} )", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and dbo.FromRecords.ClassID = {0}", CLASSID));
            }
            if (!DTIME.Year.Equals(1949))
            {
                where.Append(string.Format(" and YEAR(dbo.FromRecords.OutDate) = {0} and Month(dbo.FromRecords.OutDate) = {1}", DTIME.Year, DTIME.Month));
            }
            dt = dal.GetTotalPercent(where.ToString());
            return dt;
        }
        /// <summary>
        /// 月条件
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetTotalPercentForDay(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and dbo.FromRecords.GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" and dbo.FromRecords.GroupID IN ( {0} )", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and dbo.FromRecords.ClassID = {0}", CLASSID));
            }
            if (!DTIME.Year.Equals(1949))
            {
                where.Append(string.Format(" and YEAR(dbo.FromRecords.OutDate) = {0} and Month(dbo.FromRecords.OutDate) = {1}", DTIME.Year, DTIME.Month));
            }
            return dal.GetTotalPercentForDay(where.ToString());
        }

        /// <summary>
        /// 年条件
        /// </summary>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetTotalPercentMonth(string strarealist, string AREAID, string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (Convert.ToInt32(YEAR) > 2011)
            {
                if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
                {
                    where.Append(string.Format(" and dbo.FromRecords.GroupID = {0}", AREAID));
                }
                else if (!string.IsNullOrEmpty(strarealist))
                {
                    where.Append(string.Format(" and dbo.FromRecords.GroupID IN ( {0} )", strarealist));
                }
                if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
                {
                    where.Append(string.Format(" and dbo.FromRecords.ClassID = {0}", CLASSID));
                }
                if (!string.IsNullOrEmpty(YEAR))
                {
                    where.Append(string.Format(" and YEAR(dbo.FromRecords.OutDate) = {0}", YEAR));
                }
                dt = dal.GetTotalPercentMonth(where.ToString());
            }
            else
            {
                if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
                {
                    where.Append(string.Format(" and ClassID = {0}", CLASSID));
                }
                if (!string.IsNullOrEmpty(YEAR))
                {
                    where.Append(string.Format(" and YEAR(InDate) = {0}", YEAR));
                }
                dt = dal.GetTotalPercentMonthHistoryDate(where.ToString());
            }
            return dt;
        }
        #endregion

        #region 分类统计
        public DataTable GetTypeCharDB(string strarealist, string GroupID, string[] dTime, int[] rawId, string classId)
        {
            return dal.GetTypeCharDB(strarealist, GroupID, dTime, rawId, classId);
        }
        public DataTable GetTypeCharDBHistoryData(string strarealist, string GroupID, string[] dTime, int[] rawId, string classId)
        {
            return dal.GetTypeCharDBHistoryData(strarealist, GroupID, dTime, rawId, classId);
        }
        public DataTable GetTypeCharDBByMonth(string strarealist, string GroupID, DateTime dTime, int[] rawId, string classId)
        {
            return dal.GetTypeCharDBByMonth(strarealist, GroupID, dTime, rawId, classId);
        }
        #endregion

        #region 分类对比
        public DataTable GetXiaohao_Contrast(string strarealist, string GroupID, string ClassID, string[] dTime, int[] rawId)
        {
            return dal.GetXiaohao_Contrast(strarealist, GroupID, ClassID, dTime, rawId);
        }
        public DataTable GetPerson_Avg_ContrastHistoryByYear(string strarealist, string[] dTime, string groupid, string classId, int[] rawId)
        {
            return dal.GetPerson_Avg_ContrastHistoryByYear(strarealist, dTime, groupid, classId, rawId);
        }
        public DataTable GetXiaohao_ContrastForMonth(string strarealist, string GroupID, string ClassID, DateTime DTIME, int[] rawId)
        {
            return dal.GetXiaohao_ContrastForMonth(strarealist, GroupID, ClassID, DTIME, rawId);
        }

        public DataTable GetXiaohao_ContrastForDay(string strarealist, string GroupID, string ClassID, DateTime DTIME, int[] rawId)
        {
            return dal.GetXiaohao_ContrastForDay(strarealist, GroupID, ClassID, DTIME, rawId);
        }
        #endregion

        #region 食用油统计
        public DataTable GetView_PurchaseInfo(string DID, string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and RAWIDS = {0}", DID));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and YEAR(Date) = {0}", YEAR));
            }
            return dal.GetView_PurchaseInfo(where.ToString());
        }

        public DataTable GetView_PurchaseInfo(string DID, string CLASSID, string YEAR, string MONTH)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and RAWIDS = {0}", DID));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and YEAR(Date) = {0}", YEAR));
            }
            if (!string.IsNullOrEmpty(MONTH))
            {
                where.Append(string.Format(" and Month(Date) = {0}", MONTH));
            }
            return dal.GetView_PurchaseInfoForMonth(where.ToString());
        }

        public DataTable GetAteNumber(string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and yearofdate= {0}", YEAR));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            return dal.GetAteNumber(where.ToString());
        }

        public DataTable GetAteNumber(string CLASSID, string YEAR, string MONTH)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and yearofdate= {0}", YEAR));
            where.Append(string.Format(" and monthofdate = {0}", MONTH));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            return dal.GetAteNumberForMonth(where.ToString());
        }

        public DataTable GetRecords(string CLASSID, string YEAR, string MONTH)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and YEAR(OutDate) = {0}", YEAR));
            }
            if (!string.IsNullOrEmpty(MONTH))
            {
                where.Append(string.Format(" and Month(OutDate) = {0}", MONTH));
            }
            return dal.GetRecords(where.ToString());
        }

        public DataTable GetView_PurchaseInfoForYear(string DID, string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and RAWIDS = {0}", DID));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and YEAR(Date) = {0}", YEAR));
            }
            return dal.GetView_PurchaseInfoForYear(where.ToString());
        }

        public DataTable GetAteNumberHallYear(string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            where.Append(string.Format(" and yearofdate= {0}", YEAR));
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            return dal.GetAteNumberHallYear(where.ToString());
        }

        public DataTable GetRecordsHallYear(string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and YEAR(OutDate) = {0}", YEAR));
            }
            return dal.GetRecordsHallYear(where.ToString());
        }
        #endregion

        #region 人均用油
        public DataTable GetOilOfYearByDate(string year)
        {
            return dal.GetOilOfYearByDate(year);
        }
        #endregion

        #region 人均消耗分类汇总
        public DataTable GetPersonNumbers(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" and GroupID IN ({0})", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                where.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
            }
            return dal.GetPersonNumbers(where.ToString());
        }
        public DataTable GetPersonNumbers(string strarealist, string AREAID, string CLASSID, int YEAR, int MONTH)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" and GroupID IN ({0})", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            where.Append(string.Format(" and Year(Date) = {0} and Month(Date) = {1}", YEAR, MONTH));
            return dal.GetPersonNumbers(where.ToString());
        }

        /// <summary>
        /// 人均消耗分类汇总
        /// </summary>
        /// <param name="whereToXiaohao"></param>
        /// <param name="whereToDy"></param>
        /// <returns></returns>
        public DataTable GetPersonAvgXiaohaoType(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder whereToXiaohao = new StringBuilder();
            //StringBuilder whereToDy = new StringBuilder();
            //whereToDy.Append(" and monthofdate = MONTH(dbo.View_RawXiaoHaoCollect.OutDate)");
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                whereToXiaohao.Append(string.Format(" and AreaID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                whereToXiaohao.Append(string.Format(" and AreaID IN ({0})", strarealist));
            }

            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                whereToXiaohao.Append(string.Format(" and ClassID = {0}", CLASSID));
                //whereToDy.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals("1949"))
            {
                whereToXiaohao.Append(string.Format(" and NumYear = {0}", DTIME.Year));
                //whereToDy.Append(string.Format(" and yearofdate = {0}", DTIME.Year));
            }

            return dal.GetPersonAvgXiaohaoType(whereToXiaohao.ToString());
        }
        #endregion

        #region 日常监控

        /// <summary>
        /// 实际消耗值
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="dateTime">月份</param>
        /// <param name="rawId">原料类别</param>
        /// <param name="classId">班组ID</param>
        /// <returns></returns>
        public DataTable GetFactXiaoHao_RichangJiankong(string pid, string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            string strclasslist = new Catering_Rules.OperationArea().GetStrAreaIDListByPersonnelID(pid);
            return dal.GetFactXiaoHao_RichangJiankong(strclasslist, GroupID, dateTime, rawId, classId);
        }

        /// <summary>
        /// 标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            return dal.GetBiaoZhunXiaoHao_RichangJiankong(GroupID, dateTime, rawId, classId);
        }

        /// <summary>
        /// 日实际消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetDayFactXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            return dal.GetDayFactXiaoHao_RichangJiankong(GroupID, dateTime, rawId, classId);
        }
        /// <summary>
        /// 月实际消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetMonthFactXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            return dal.GetMonthFactXiaoHao_RichangJiankong(GroupID, dateTime, rawId, classId);
        }
        /// <summary>
        /// 日标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetDayBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            return dal.GetDayBiaoZhunXiaoHao_RichangJiankong(GroupID, dateTime, rawId, classId);
        }

        /// <summary>
        /// 按班组统计日实际消耗值
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="dateTime">月份</param>
        /// <param name="rawId">原料类别</param>
        /// <param name="classId">班组ID</param>
        /// <returns></returns>
        public DataTable GetBanZuFactXiaoHao_RichangJiankong(string pid, string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            string strclasslist = new Catering_Rules.OperationArea().GetStrAreaIDListByPersonnelID(pid);
            return dal.GetBanZuFactXiaoHao_RichangJiankong(strclasslist, GroupID, dateTime, rawId, classId);
        }

        /// <summary>
        /// 按班组统计日标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetBanZuBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            return dal.GetBanZuBiaoZhunXiaoHao_RichangJiankong(GroupID, dateTime, rawId, classId);
        }

        #endregion

        #region 月累计百分比
        public DataTable GetMonthOfCountAvg(string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME.Year > 2011)
            {
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(OutDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(OutDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GetMonthOfCountAvg(where.ToString());
            }
            else
            {
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(inDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(inDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GetMonthOfCountAvgHistoryData(where.ToString());
            }
            return dt;
        }
        public DataTable GetPersons(string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && DTIME.Year != 1949)
            {
                where.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
                if (DTIME.Second != 11)
                {
                    where.Append(string.Format(" and Month(Date) = {0} ", DTIME.Month));
                }
            }
            return dal.GetPersonNumbers(where.ToString());
        }
        #endregion

        #region 人均食用油统计

        public DataTable GetPersonOilAvg(int AREAID, int CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (DTIME.Year > 2011)
            {
                dt = dal.GetClassPersonOilByYear(AREAID, CLASSID, DTIME);
            }
            else
            {
                if (CLASSID > 0)
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.id = {0}", CLASSID));
                }
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(inDate) = {0}", DTIME.Year));
                }
                dt = dal.GetPersonOilAvgHistory(where.ToString());
            }
            return dt;
        }

        /// <summary>
        /// 获取作业区油脂类的消耗统计
        /// </summary>
        /// <param name="areaid"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public decimal GetGroupPersonOilByYear(int areaid, DateTime dtime)
        {
            DataTable dt = dal.GetGroupPersonOilByYear(areaid, dtime);
            if (dt != null && dt.Rows.Count > 0)
            {
                decimal numbers = Convert.ToDecimal(dt.Rows[0]["Numbers"]);
                decimal eat = Convert.ToDecimal(dt.Rows[0]["EatNumber"]);
                if (eat > 0) return numbers / eat;
            }
            return 0;
        }

        public DataTable GetAreaOilAvg(string AREAID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", AREAID));
            }
            if (DTIME != null && DTIME.Year != 1949)
            {
                where.Append(string.Format(" and Year(OutDate) = {0}", DTIME.Year));
                if (DTIME.Second != 11)
                {
                    where.Append(string.Format(" and Month(OutDate) = {0} ", DTIME.Month));
                }
            }
            return dal.GetAreaOilAvg(where.ToString());
        }


        public DataTable GerPersonOilAvgGroupByClassID(string StrArealist, string AREAID, string ClassID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(StrArealist))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.Areaid IN({0}) ", StrArealist));
            }

            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.ID = {0}", ClassID));
            }

            if (DTIME.Year > 2011)
            {
                //if (DTIME != null && DTIME.Year != 1949)
                //{
                //    where.Append(string.Format(" and Year(dbo.FromRecords.OutDate) = {0}", DTIME.Year));
                //    if (DTIME.Second != 11)
                //    {
                //        where.Append(string.Format(" and Month(dbo.FromRecords.OutDate) = {0} ", DTIME.Month));
                //    }
                //}
                dt = dal.GerPersonOilAvgGroupByClassID(where.ToString(), DTIME);
            }
            else
            {
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(InDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(InDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GerPersonOilAvgHistoryGroupByClassID(where.ToString());
            }
            return dt;
        }

        public DataTable GerPersonOilAvgGroupByGroupID(string StrArealist, string AREAID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (DTIME.Year > 2011)
            {
                if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
                {
                    where.Append(string.Format(" and Areaid = {0}", AREAID));
                }
                else if (!string.IsNullOrEmpty(StrArealist))
                {
                    where.Append(string.Format(" and Areaid IN({0}) ", StrArealist));
                }

                //if (DTIME != null && DTIME.Year != 1949)
                //{
                //    where.Append(string.Format(" and Year(dbo.FromRecords.OutDate) = {0}", DTIME.Year));
                //    if (DTIME.Second != 11)
                //    {
                //        where.Append(string.Format(" and Month(dbo.FromRecords.OutDate) = {0} ", DTIME.Month));
                //    }
                //}
                dt = dal.GerPersonOilAvgGroupByGroupID(where.ToString(),DTIME);
            }
            else
            {
                if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", AREAID));
                }
                else if (!string.IsNullOrEmpty(StrArealist))
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.Areaid IN({0}) ", StrArealist));
                }
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(InDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(InDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GerPersonOilAvgHistoryGroupByGroupID(where.ToString());
            }
            return dt;
        }


        public DataTable GetPersonOilAvgForMonth(string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.id = {0}", CLASSID));
            }
            if (DTIME.Year > 2011)
            {
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(OutDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(OutDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GetPersonOilAvg(where.ToString());
            }
            else
            {
                if (DTIME != null && DTIME.Year != 1949)
                {
                    where.Append(string.Format(" and Year(InDate) = {0}", DTIME.Year));
                    if (DTIME.Second != 11)
                    {
                        where.Append(string.Format(" and Month(InDate) = {0} ", DTIME.Month));
                    }
                }
                dt = dal.GetPersonOilAvgHistory(where.ToString());
            }
            return dt;
        }
        #endregion

        #region 平台人均分类对比
        /// <summary>
        /// 平台人均分类对比
        /// </summary>
        /// <param name="year">时间</param>
        /// <param name="classId">班组id，可以传null</param>
        /// <param name="rawTId">原料大类，可以传null</param>
        /// <returns>返回DataTable类型</returns>
        public DataTable GetPerson_Avg_ContrastByYear(string strarealist, string[] year, string groupid, string classId, int[] rawTId)
        {
            return dal.GetPerson_Avg_ContrastByYear(strarealist, year, groupid, classId, rawTId);
        }
        public DataTable GetPerson_Avg_ContrastByYearHistory(string strarealist, string[] dTime, string groupid, string classId, int[] rawId)
        {
            return dal.GetPerson_Avg_ContrastByYearHistory(strarealist, dTime, groupid, classId, rawId);
        }
        #endregion

        #region 人均消耗统计
        public DataTable GetRenShuHistory(string year, string groupid, string classId)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", groupid));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.id = {0}", classId));
            }
            if (!string.IsNullOrEmpty(year))
            {
                where.Append(string.Format(" and Year(InDate) = {0}", year));
            }
            return dal.GetRenShuHistory(where.ToString());
        }
        /// <summary>
        /// 人均消耗统计
        /// </summary>
        /// <param name="dTime">时间</param>
        /// <param name="classId">班组id，没有条件传null</param>
        /// <param name="rawTId">原料大类，没有条件传null</param>
        /// <returns></returns>
        public DataTable GetRenJunXHTongjiByYear(string year, string strarealist, string groupid, string classId, int[] rawId)
        {
            DataTable dt = new DataTable();
            if (Convert.ToInt32(year) > 2011)
            {
                dt = dal.GetRenJunXHTongjiByYear(year, strarealist, groupid, classId, rawId);
            }
            else
            {
                StringBuilder where = new StringBuilder();
                if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", groupid));
                }
                else if (!string.IsNullOrEmpty(strarealist))
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.Areaid in ( {0} )", strarealist));
                }
                if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
                {
                    where.Append(string.Format(" and  dbo.ClassInfo.id = {0}", classId));
                }
                if (!string.IsNullOrEmpty(year))
                {
                    where.Append(string.Format(" and Year(InDate) = {0}", year));
                }
                dt = dal.GetRenJunXHTongjiByYearHistory(where.ToString());
            }
            return dt;
        }
        #endregion

        #region 人均分类统计
        /// <summary>
        /// 人均分类统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonAvgContrastForLineChar(string strclasslist, string year, string classId, int[] rawId)
        {
            DataTable dt = new DataTable();
            if (Convert.ToInt32(year) > 2011)
            {
                dt = dal.GetPersonAvgContrastForLineChar(strclasslist, year, classId, rawId);
            }
            else
            {
                dt = dal.GetPersonAvgContrastForLineCharHistory(strclasslist, year, classId, rawId);
            }
            return dt;
        }
        #endregion

        #region 人均食用油汇总

        /// <summary>
        /// 每月食用油消耗汇总
        /// </summary>
        /// <param name="strarealist"></param>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetXiaoHaoAvg(string strarealist, string AREAID, string CLASSID, int YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  f.GroupID = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and  f.ClassID = {0}", CLASSID));
            }
            if (YEAR>1949)
            {
                where.Append(string.Format(" and YEAR(f.OutDate) = {0}", YEAR));
            }
            dt = dal.GetXiaoHaoAvg(where.ToString());
            return dt;
        }

        /// <summary>
        /// 每月食用油采购计划汇总
        /// </summary>
        /// <param name="strarealist"></param>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetPurchase(string strarealist, string AREAID, string CLASSID, int YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  p.GroupID = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and  p.ClassID = {0}", CLASSID));
            }
            if (YEAR > 1949)
            {
                where.Append(string.Format(" and YEAR(p.[Date]) = {0}", YEAR));
            }
            dt = dal.GetPurchase(where.ToString());
            return dt;
        }

        /// <summary>
        /// 每月食用油就餐人数汇总
        /// </summary>
        /// <param name="strarealist"></param>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetDynamic(string strarealist, string AREAID, string CLASSID, int YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  d.GroupID = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and  d.ClassID = {0}", CLASSID));
            }
            if (YEAR > 1949)
            {
                where.Append(string.Format(" and YEAR(d.[Date]) = {0}", YEAR));
            }
            dt = dal.GetDynamic(where.ToString());
            return dt;
        }

        /// <summary>
        /// 2011年以前的历史数据
        /// </summary>
        /// <param name="strarealist"></param>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <param name="MONTH"></param>
        /// <returns></returns>
        public DataTable GetOilPersonAvgHistoryContrant(string strarealist, string AREAID, string CLASSID, string YEAR, string MONTH)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.Areaid = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and  dbo.ClassInfo.id = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and Year(InDate) = {0}", YEAR));
            }
            dt = dal.GetOilPersonAvgHistory(where.ToString());
            return dt;
        }

        /// <summary>
        /// 2011年以前的历史数据
        /// </summary>
        /// <param name="strarealist"></param>
        /// <param name="AREAID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="YEAR"></param>
        /// <returns></returns>
        public DataTable GetOilPersonAvgHistoryForChar(string strarealist, string AREAID, string CLASSID, string YEAR)
        {
            StringBuilder where = new StringBuilder();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" and GroupID IN ({0}) ", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(YEAR))
            {
                where.Append(string.Format(" and Year(Indate) = {0}", YEAR));
            }
            dt = dal.GetOilPersonAvgHistoryForChar(where.ToString());
            return dt;
        }

        #endregion

        #region 成本数据统计

        /// <summary>
        /// 期初、期末库存总量
        /// </summary>
        /// <param name="OAREAID"></param>
        /// <param name="TYPE"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public decimal GetFirstInCount(DateTime datetime, int groupid)
        {
            return dal.GetCountResult(datetime, groupid);
        }

        /// <summary>
        /// 获取库存总量，期初与期末可共用
        /// </summary>
        /// <param name="datetime"></param>
        /// <param name="RAWTID"></param>
        /// <param name="classid"></param>
        /// <returns></returns>
        public decimal GetCountResultByClass(DateTime datetime, int RAWTID, int classid)
        {
            return dal.GetCountResultByClass(datetime, RAWTID, classid);
        }

        /// <summary>
        /// 采购数口径（入库）
        /// </summary>
        /// <param name="OAREAID"></param>
        /// <param name="STARTTIME"></param>
        /// <param name="ENDTIME"></param>
        /// <returns></returns>
        public decimal GetProcureCount(string OAREAID, DateTime STARTTIME, DateTime ENDTIME)
        {
            decimal Result = 0;
            string StrWhere = " 1=1 ";
            if (!string.IsNullOrEmpty(OAREAID) && !OAREAID.Equals("-10"))
            {
                StrWhere += string.Format(" AND GroupID = {0}", OAREAID);
            }
            if (STARTTIME != null && STARTTIME.Year > 1949 && ENDTIME != null && ENDTIME.Year > 1949)
            {
                StrWhere += string.Format(" AND IntDate>='{0}' AND IntDate<'{1}' ", STARTTIME.ToShortDateString(), ENDTIME.AddMonths(1).ToShortDateString());
            }
            DataTable dt = new InDbInfo().GetList(StrWhere).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var d = from o in dt.AsEnumerable() select new { number = Convert.ToDecimal(o.Field<string>("Number")), price = Convert.ToDecimal(o.Field<string>("Price")) };
                Result = d.ToList().Sum(o => o.number * o.price);
            }
            return Result;
        }

        /// <summary>
        /// 采购数口径（入库）
        /// </summary>
        /// <param name="ClassID">班组</param>
        /// <param name="RAWTID">原料大类</param>
        /// <param name="STARTTIME">开始时间</param>
        /// <param name="ENDTIME">结束时间</param>
        /// <returns>入库总金额</returns>
        public decimal GetProcureCountByClass(string ClassID, int RAWTID, DateTime STARTTIME, DateTime ENDTIME)
        {
            decimal Result = 0;
            string StrWhere = " 1=1 ";
            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                StrWhere += string.Format(" AND ClassID = {0}", ClassID);
            }
            if (RAWTID > 0)
            {
                StrWhere += string.Format(" AND RAWTID = {0}", RAWTID);
            }
            if (STARTTIME != null && STARTTIME.Year > 1949 && ENDTIME != null && ENDTIME.Year > 1949)
            {
                StrWhere += string.Format(" AND IntDate>='{0}' AND IntDate<'{1}' ", STARTTIME.ToShortDateString(), ENDTIME.AddMonths(1).ToShortDateString());
            }
            DataTable dt = new InDbInfo().GetList(StrWhere).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var d = from o in dt.AsEnumerable() select new { number = Convert.ToDecimal(o.Field<string>("Number")), price = Convert.ToDecimal(o.Field<string>("Price")) };
                Result = d.ToList().Sum(o => o.number * o.price);
            }
            return Result;
        }

        #endregion

        #region 成本比例
        public DataTable GetViewFromRecords(string where)
        {
            return dal.GetViewFromRecords(where);
        }
        #endregion

        #region 月成本明细
        public DataTable GetCostDataPersonAvg(string oAreaId, string classId, DateTime time)
        {
            StringBuilder whereFromRecords = new StringBuilder();
            StringBuilder wheredy = new StringBuilder();
            if (!string.IsNullOrEmpty(oAreaId) && !oAreaId.Equals("-10"))
            {
                whereFromRecords.Append(string.Format(" and GroupID = {0}", oAreaId));
                wheredy.Append(string.Format(" and GroupID = {0}", oAreaId));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                whereFromRecords.Append(string.Format(" and ClassID = {0}", classId));
                wheredy.Append(string.Format(" and ClassID = {0}", classId));
            }
            if (time != null)
            {
                whereFromRecords.Append(string.Format(" and numyear = {0}", time.Year));
                wheredy.Append(string.Format(" and year(date) = {0}", time.Year));
            }
            return dal.GetCostDataPersonAvg(wheredy.ToString(), whereFromRecords.ToString());
        }
        #endregion

        #region 消耗分类横向对比图
        /// <summary>
        /// 消耗分类横向对比图
        /// </summary>
        /// <param name="rawTID"></param>
        /// <param name="classId"></param>
        /// <param name="dTime"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoTypeCrossChar(int[] rawTIDs, ArrayList classIds, string[] years)
        {
            StringBuilder where = new StringBuilder();
            if (rawTIDs != null && rawTIDs.Count() > 0)
            {
                where.Append(" AND (F.RAWTID = " + rawTIDs[0]);
                for (int i = 1; i < rawTIDs.Count(); i++)
                {
                    where.Append(" or F.RAWTID = " + rawTIDs[i]);
                }
                where.Append(")");
            }
            if (classIds != null && classIds.Count > 0)
            {
                where.Append(" AND (F.ClassID = " + classIds[0]);
                for (int i = 1; i < classIds.Count; i++)
                {
                    where.Append(" or F.ClassID=" + classIds[i]);
                }
                where.Append(")");
            }
            if (years != null && years.Count() > 0)
            {
                where.Append(" AND (year(F.outdate) = " + years[0]);
                for (int i = 0; i < years.Count(); i++)
                {
                    where.Append(string.Format(" or year(F.outdate)={0}", years[i]));
                }
                where.Append(")");
            }
            return dal.GetXiaohaoTypeCrossChar(where.ToString());
        }
        #endregion

        #region 消耗分类纵向对比图

        public DataTable GetXiaohaoTypePortait(int[] RAWTID, string CLASSID, DateTime DTIME)
        {
            return dal.GetXiaohaoTypePortait(RAWTID, CLASSID, DTIME);
        }

        /// <summary>
        /// 第三方折线图
        /// </summary>
        /// <param name="RAWTID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="AREAID"></param>
        /// <param name="CompanyID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoTypePortaitThird(int[] RAWTID, string CLASSID, string AREAID, string CompanyID, DateTime DTIME)
        {
            return dal.GetXiaohaoTypePortaitThird(RAWTID, CLASSID, AREAID, CompanyID, DTIME);
        }
        #endregion

        #region 年度伙食统计表
        public DataTable GetHallYearAte(string OAREAID, string CLASSID, DateTime DTIME)
        {
            string whereFromRecords = "";
            if (!string.IsNullOrEmpty(OAREAID) && !OAREAID.Equals("-10"))
            {
                whereFromRecords += string.Format(" and GroupID = {0}", OAREAID);
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                whereFromRecords += string.Format(" and ClassID = {0}", CLASSID);
            }
            if (DTIME != null)
            {
                whereFromRecords += string.Format(" and Year(OutDate)= {0}", DTIME.Year);
            }
            return dal.GetHallYearAte(whereFromRecords);
        }
        #endregion

        #region 人工成本

        public DataTable GetLaborCostsList(string OAREAID, string CLASSID, DateTime DTIME, string CompanyID)
        {
            DataTable dt = new DataTable();
            string whereClause = string.Format(" and CompanyID = {0}", CompanyID);
            if (!string.IsNullOrEmpty(OAREAID) && !OAREAID.Equals("-10"))
            {
                whereClause += string.Format(" and GroupID = {0}", OAREAID);
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                whereClause += string.Format(" and ClassID = {0}", CLASSID);
            }
            if (DTIME != null)
            {
                whereClause += string.Format(" and Year(Date)= {0}", DTIME.Year);
            }
            return new LaborCosts().GetList(whereClause).Tables[0];
        }

        #endregion

        #region 自付费杂品明细
        public DataTable GetGroceryDetail(string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder whereStr = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                whereStr.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                whereStr.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null)
            {
                whereStr.Append(string.Format(" and [Year]={0} ", DTIME.Year));
            }
            return dal.GetGroceryDetail(whereStr.ToString());
        }
        #endregion

        #region 就餐人员年/月统计
        /// <summary>
        /// 就餐人员年/月统计
        /// </summary>
        /// <param name="GROUPID">作业区ID</param>
        /// <param name="CLASSID">班组ID</param>
        /// <param name="DTIME">时间</param>
        /// <returns>DataTable</returns>
        public DataTable GetDynamicAte(string strclasslist, string GROUPID, string CLASSID, DateTime DTIME)
        {
            return dal.GetDynamicAte(strclasslist, GROUPID, CLASSID, DTIME);
        }

        public DataTable GetDynamicAte(string strclasslist, string GROUPID, string CLASSID, DateTime DTStart, DateTime DTEnd)
        {
            return dal.GetDynamicAte(strclasslist, GROUPID, CLASSID, DTStart, DTEnd);
        }
        #endregion

        #region 配餐人员动态
        public DataTable GetPersonCount()
        {
            return dal.GetPersonCount();
        }

        public DataTable GetSetingPerson(string CLASSID, string IsWorking)
        {
            return dal.GetSetingPerson(CLASSID, IsWorking);
        }

        public DataTable GetCompanyPerson(DateTime date)
        {
            StringBuilder where = new StringBuilder();
            if (date != DateTime.MinValue)
            {
                where.Append(string.Format(" and Date = '{0}'", date.ToString("yyyy-MM-dd")));
            }

            return dal.GetCompanyPerson(where.ToString());
        }
        public DataTable GetPersonModel(string id)
        {
            return dal.GetPersonModel(id);
        }

        /// <summary>
        /// 获取配餐人员动态列表
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="ClassID">班组ID</param>
        /// <param name="DTime">时间</param>
        /// <param name="isZiYingCompany">状态：true：包含自营公司、false：不包含自营公司</param>
        /// <returns></returns>
        public DataTable GetAttendanceRecordReport(string strarealist, string GroupID, string ClassID, DateTime DTime, bool isZiYingCompany)
        {
            return dal.GetAttendanceRecordReport(strarealist, GroupID, ClassID, DTime, isZiYingCompany);
        }

        public DataTable GetZiYingCompanyAttendanceRecord()
        {
            return dal.GetZiYingCompanyAttendanceRecord();
        }

        /// <summary>
        /// 获取更公司人数统计
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="ClassID"></param>
        /// <param name="DTime"></param>
        /// <returns></returns>
        public DataTable GetAttendanceRecordByMSN(string GroupID, string ClassID, DateTime DTime)
        {
            return dal.GetAttendanceRecordByMSN(GroupID, ClassID, DTime);
        }

        /// <summary>
        /// 获取更公司人数统计
        /// </summary>
        /// <param name="DTime"></param>
        /// <returns></returns>
        public DataTable GetZiYingAttendanceRecordByMSN(DateTime DTime)
        {
            return dal.GetZiYingAttendanceRecordByMSN(DTime);
        }

        /// <summary>
        /// 获取配餐人员动态列表
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="ClassID">班组ID</param>
        /// <param name="DTime">时间</param>
        /// <param name="isZiYingCompany">状态：true：包含自营公司、false：不包含自营公司</param>
        /// <returns></returns>
        public DataTable GetPeicanDongTaiByDate(string strarealist, string GroupID, string ClassID, DateTime DTime, bool isZiYingCompany)
        {
            return dal.GetPeicanDongTaiByDate(strarealist, GroupID, ClassID, DTime, isZiYingCompany);
        }

        #endregion

        #region 月考勤
        public DataTable GetWorkOfMonth(string CLASSID, string TEXT, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(TEXT))
            {
                where.Append(string.Format(" and RID "));
            }
            if (DTIME != null && DTIME.Year != 1949)
            {
                where.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
                if (DTIME.Second != 11)
                {
                    where.Append(string.Format(" and Month(Date) = {0} ", DTIME.Month));
                }
            }
            return dal.GetWorkOfMonth(where.ToString());
        }
        #endregion

        #region 年考勤
        public DataTable GetWorkOfYear(string CLASSID, string TEXT, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(TEXT))
            {
                //where.Append(string.Format(" and RID "));
            }
            if (DTIME != null && DTIME.Year != 1949)
            {
                where.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
            }
            return dal.GetWorkOfYear(where.ToString());
        }
        #endregion

        #region 服务比列汇总
        public DataTable GetServiceCollect(string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            string groupby = "";
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                where.Append(string.Format(" and Year(Date) = {0}", DTIME.Year));
                if (!DTIME.Second.Equals(11))
                {
                    where.Append(string.Format(" and Month(Date) = {0}", DTIME.Month));
                }
            }
            return dal.GetServiceCollect(where.ToString(), groupby);
        }
        #endregion

        #region 全年服务总表
        public DataTable GetHallYearCollect(string strarealist, string AREAID, string CLASSID, DateTime DTIME)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and GroupID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" AND GroupID IN ({0}) ", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and ClassID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                where.Append(string.Format(" and yearofdate = {0}", DTIME.Year));
            }
            return dal.GetHallYearCollect(where.ToString());
        }
        #endregion

        #region 人均服务比例图

        /// <summary>
        /// 人均服务比例图
        /// </summary>
        /// <param name="where">and year(A.Date)=2012 and C.id=1 and B.id=45</param>
        /// <returns></returns>
        public DataTable GetPersonAvgService(string strarealist, string AREAID, string CLASSID, DateTime DTIME, bool isMonth)
        {
            StringBuilder where = new StringBuilder();
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                where.Append(string.Format(" and B.AreaID = {0}", AREAID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                where.Append(string.Format(" AND B.AreaID IN ({0}) ", strarealist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                where.Append(string.Format(" and B.ID = {0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                where.Append(string.Format(" and year(A.Date)={0} ", DTIME.Year));
                if (isMonth)
                {
                    where.Append(string.Format(" and month(A.Date) = {0} ", DTIME.Month));
                }
            }
            return dal.GetPersonAvgService(where.ToString());
        }
        #endregion

        #region 日报统计

        /// <summary>
        /// 日报人数获取
        /// </summary>
        /// <param name="StrCompanyID">公司</param>
        /// <param name="StrGroupID">作业区</param>
        /// <param name="StrClassID">班组</param>
        /// <param name="StartDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="DateType">时间查询类型</param>
        /// <param name="isContainZiYing">是否包含自营公司</param>
        /// <returns></returns>
        public DataTable GetView_RiBaoData(string StrCompanyID, string StrGroupID, string StrClassID, DateTime StartDate, DateTime EndDate, string DateType, bool isContainZiYing)
        {
            string whereClause = "";
            int companyID = 0;
            int groupID = -1;
            int classID = -1;

            Int32.TryParse(StrCompanyID, out companyID);
            Int32.TryParse(StrGroupID, out groupID);
            Int32.TryParse(StrClassID, out classID);

            if (StartDate.Year < 1949) return null;
            if (companyID < 1) return null;

            whereClause = string.Format(" AND CompanyID={0} ", companyID);
            if (groupID > 0) whereClause += string.Format(" AND GroupID={0} ", groupID);
            if (classID > 0) whereClause += string.Format(" AND ClassID={0} ", classID);

            if (DateType == "Year")
            {
                whereClause += string.Format(" AND [YEAR]={0} ", StartDate.Year);
            }
            else if (DateType == "Month")
            {
                whereClause += string.Format(" AND [YEAR]={0} AND [MONTH]={1} ", StartDate.Year, StartDate.Month);
            }
            else
            {
                whereClause += string.Format(" AND [Date] BETWEEN '{0}' AND '{1}' ", StartDate.ToShortDateString(), EndDate.ToShortDateString());
            }
            if (!isContainZiYing)
            {
                whereClause += string.Format(" AND [CName]<>'{0}' ", "自营公司");
            }

            return dal.GetView_RiBaoData(whereClause);
        }

        /// <summary>
        /// 获取其他单位人员数据
        /// </summary>
        /// <param name="StrCompanyID">公司</param>
        /// <param name="StrGroupID">作业区</param>
        /// <param name="StrClassID">班组</param>
        /// <param name="StartDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="DateType">时间查询类型</param>
        /// <param name="isContainZiYing">是否包含自营公司</param>
        /// <returns></returns>
        public DataTable GetRiBaoData_Other(string StrCompanyID, string StrGroupID, string StrClassID, DateTime StartDate, DateTime EndDate, string DateType, bool isContainZiYing)
        {
            string whereClause = "";
            int companyID = 0;
            int groupID = -1;
            int classID = -1;

            Int32.TryParse(StrCompanyID, out companyID);
            Int32.TryParse(StrGroupID, out groupID);
            Int32.TryParse(StrClassID, out classID);

            if (StartDate.Year < 1949) return null;
            if (companyID < 1) return null;

            whereClause = string.Format(" AND CompanyID={0} ", companyID);
            if (groupID > 0) whereClause += string.Format(" AND GroupID={0} ", groupID);
            if (classID > 0) whereClause += string.Format(" AND ClassID={0} ", classID);

            if (DateType == "Year")
            {
                whereClause += string.Format(" AND YEAR([Dates])={0} ", StartDate.Year);
            }
            else if (DateType == "Month")
            {
                whereClause += string.Format(" AND YEAR([Dates])={0} AND MONTH([Dates])={1} ", StartDate.Year, StartDate.Month);
            }
            else
            {
                whereClause += string.Format(" AND [Dates] BETWEEN '{0}' AND '{1}' ", StartDate.ToShortDateString(), EndDate.ToShortDateString());
            }
            if (!isContainZiYing)
            {
                whereClause += string.Format(" AND [CName]<>'{0}' ", "自营公司");
            }
            return dal.GetRiBaoData_Other(whereClause);

        }

        #endregion
    }
}
